﻿using System;
using System.Collections.Generic;
using SurveySystem.Model;
using MySql.Data.MySqlClient;
using SurveySystem.Utility;

namespace SurveySystem.Repository.MySQL
{
    public class MySQLResponseAnswerRepository : ISurveyResponseAnswerRepository
    {

        #region " Private Utility Methods "
        private static ResponseAnswer ResponseAnswerFromDataReader(MySqlDataReader dr)
        {
            ResponseAnswer ra = new ResponseAnswer();

            ra.ResponseID = dr.GetInt32(0);
            if (!(dr.GetValue(1) is System.DBNull))
                ra.AnswerID = dr.GetInt32(1);
            if (!(dr.GetValue(2) is System.DBNull))
                ra.QuestionID = dr.GetInt32(2);
            if (!(dr.GetValue(3) is System.DBNull))
                ra.Comment = dr.GetString(3);

            return ra;
        }
        #endregion

        public List<ResponseAnswer> GetResponseAnswersForResponse(int responseID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<ResponseAnswer> list = new List<ResponseAnswer>();

            sql = "SELECT `response_id`, `answer_id`, `question_id`, `comment` \n" + //
                  "FROM `survey_response_answer` WHERE response_id=?id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", responseID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    ResponseAnswer ra = ResponseAnswerFromDataReader(dr);

                    list.Add(ra);
                }


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<ResponseAnswer> GetResponseAnswersForQuestion(int questionID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<ResponseAnswer> list = new List<ResponseAnswer>();

            sql = "SELECT `response_id`, `answer_id`, `question_id`, `comment` \n" + //
                  "FROM `survey_response_answer` WHERE question_id=?id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", questionID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    ResponseAnswer ra = ResponseAnswerFromDataReader(dr);
                    list.Add(ra);
                }


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<ResponseAnswer> GetResponseAnswersForAnswer(int answerID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<ResponseAnswer> list = new List<ResponseAnswer>();

            sql = "SELECT `response_id`, `answer_id`, `question_id`, `comment` \n" + //
                  "FROM `survey_response_answer` WHERE answer_id=?id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", answerID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    ResponseAnswer ra = ResponseAnswerFromDataReader(dr);
                    list.Add(ra);
                }


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<ResponseAnswer> GetResponseAnswersForSurvey(int surveyID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<ResponseAnswer> list = new List<ResponseAnswer>();

            sql = "SELECT sra.`response_id`, sra.`answer_id`, sra.`question_id`, sra.`comment` \n" + //
                  "FROM `survey_response_answer` sra \n " + //
                  "INNER JOIN `survey_response` sr ON sr.`response_id` = sra.`response_id`" + //
                  "WHERE sra.`survey_id`=?id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", surveyID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    ResponseAnswer ra = ResponseAnswerFromDataReader(dr);
                    list.Add(ra);
                }


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public void AddResponseAnswer(ResponseAnswer ra)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;

            sql = "INSERT INTO  `survey_response_answer` \n" + //
                  "(`response_id`, `question_id`, `answer_id`, `comment`) \n" + //
                  "VALUES \n" + //
                  "(?response_id, ?question_id, ?answer_id, ?comment)";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?response_id", ra.ResponseID);
                cmd.Parameters.AddWithValue("?question_id", ra.QuestionID);
                cmd.Parameters.AddWithValue("?answer_id", ra.AnswerID);
                cmd.Parameters.AddWithValue("?comment", ra.Comment);

                // add the new record to the database
                conn.Open();
                cmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

        }

        public void DeleteResponseAnswer(int responseID, int questionID, int answerID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;

            sql = "DELETE FROM `survey_response_answer` \n" + //
                  "      WHERE `response_id` = ?response_id  \n" + //
                  "        AND `question_id` = ?question_id \n" + //
                  "        AND   `answer_id` = ?answer_id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?response_id", responseID);
                cmd.Parameters.AddWithValue("?question_id", questionID);
                cmd.Parameters.AddWithValue("?answer_id", answerID);

                // add the new record to the database
                conn.Open();
                cmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }
    }
}
